package www.lagou.app;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;
import www.lagou.entity.Account;
import www.lagou.entity.Dept;
import www.lagou.entity.Employee;
import www.lagou.entity.Phone;
import www.lagou.utils.DateUtils;
import www.lagou.utils.DruidUtils;

import java.sql.SQLException;
import java.util.List;

public class Demo {

    /**
     * 测试转账功能
     * @throws SQLException
     */
    @Test
    public void testTransfer() throws SQLException {

        // 创建QueryRunner对象
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

        // 编写SQL先查询余额
        String sql = "select * from account where card = ?";

        // 执行查询，查询对应余额，留作后用
        Account account1 = qr.query(sql, new BeanHandler<Account>(Account.class), "1122334455");
        Account account2 = qr.query(sql, new BeanHandler<Account>(Account.class), "55443332211");

        // 判断余额是否足够
        if (account1.getBalance() < 5000) {
            System.out.println("余额不足！");
            return;
        } else {

            // 编写SQL更新账户信息
            String sql1 = "update account set balance = ? where card = ?";

            // 编写SQL记录转账信息
            String sql2 = "insert into transaction values(?, ?, ?, ?, ?)";

            // 配置更新账户信息的参数
            Object[] param1 = {account1.getBalance() - 5000, "1122334455"};
            Object[] param2 = {account2.getBalance() + 5000, "55443332211"};

            // 执行转账操作
            qr.update(sql1, param1);

            // 配置交易记录的参数
            Object[] param3 = {null, "1122334455", "转出", 5000, DateUtils.getDateFormart()};

            // 记录交易
            qr.update(sql2, param3);

            // 执行转账操作
            qr.update(sql1, param2);

            // 配置交易记录的参数
            Object[] param4 = {null, "55443332211", "转入", 5000, DateUtils.getDateFormart()};

            // 记录交易
            qr.update(sql2, param4);

            // 提示交易成功
            System.out.println("转账成功！");
        }
    }


    /**
     * 查询价格高于2000元，生产日期是2019年之前的所有手机信息
     */
    @Test
    public void testFindSpecialPhone() throws SQLException {

        // 创建QueryRunner对象
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

        // 编写SQL语句进行查询
        String sql = "select * from phone where price > 2000 and prodate < '2019-1-1'";

        // 使用BeanListHandler接收查询到的数据
        List<Phone> phones = qr.query(sql, new BeanListHandler<Phone>(Phone.class));

        // 循环打印结果
        for (Phone phone : phones) {
            System.out.println(phone);
        }
    }

    /**
     * 查询所有颜色是白色的手机信息
     * @throws SQLException
     */
    @Test
    public void testFindWhitePhones() throws SQLException {

        // 创建QueryRunner对象
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

        // 编写SQL语句进行查询
        String sql = "select * from phone where color like '%白色%'";

        // 使用BeanListHandler接收数据
        List<Phone> phones = qr.query(sql, new BeanListHandler<>(Phone.class));

        // 循环打印结果
        for (Phone phone : phones) {
            System.out.println(phone);
        }
    }

    /**
     * 查询所有员工信息（不包含没有部门的员工）
     * @throws SQLException
     */
    @Test
    public void testQueryEmployees() throws SQLException {

        // 创建QueryRunner对象
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

        // 编写SQL语句进行查询
        String sql = "select * from employee where did is not null";

        // 使用BeanListHandler接收数据
        List<Employee> employees = qr.query(sql, new BeanListHandler<>(Employee.class));

        // 循环打印结果
        for (Employee employee : employees) {
            System.out.println(employee);
        }
    }

    @Test
    public void testQueryAllEmployees() throws SQLException {

        // 创建QueryRunner对象
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

        // 编写SQL语句进行查询
        String sql = "select * from employee";

        // 使用BeanListHandler接收数据
        List<Employee> employees = qr.query(sql, new BeanListHandler<>(Employee.class));

        // 循环封装部门信息并打印
        for (Employee employee : employees) {
            if (employee.getDid() != 0) {
                int did = employee.getDid();
                String sql1 = "select deptname from dept where id = ?";
                Dept dept = qr.query(sql1, new BeanHandler<>(Dept.class), did);
                employee.setDept(dept);
                System.out.println("姓名：" + employee.getName() + "，薪资：" + employee.getSalary() + "，所属部门：" + employee.getDept().getDeptname());
            } else {
                employee.setDept(new Dept(0, "无"));
                System.out.println("姓名：" + employee.getName() + "，薪资：" + employee.getSalary() + "，所属部门：" + employee.getDept().getDeptname());
            }
        }
    }
}
